package hezi.helper;

import hezi.core.Application;
import hezi.core.Forum;
import hezi.core.Image;
import hezi.core.Post;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;


import org.apache.log4j.Logger;

import dz.common.DBHelper;
import dz.common.StringTool;
import dz.common.TimeTool;

public class DBUpgradeHelper {
	static Map<String,String> globaldomainmapper = new HashMap<String,String>();
	public static Logger logger = Logger.getLogger("");

	//	static String olddb ="C:\\olddb\\archive.db";
	static SimpleDateFormat sdf = new SimpleDateFormat("yyMMdd");

	static Connection conn;
	static int count = 0;
	static Statement oldstat;
	
	static Forum currFourm=null;
	
	
	
	
	static String newdb = "C:\\forums.db";
	static String olddb = ""; 
	
	public static void main(String[] args) {
		
//		initDomainMapper();
		Application.initForums();
//		int count = 0;
		

		
//		olddb ="C:\\olddb\\archive.db";
//		logger.info("---------------------------------------------------------------\n");
//		logger.info(count + " " + olddb);
//		processSingleForum();
		
		
		
//		for(Forum forum : Forum.forumsmap.values()){
//			badurls = new HashSet<String>();
//			currFourm = forum;
//			count++;
//			if(count == 1) {
//			olddb ="C:\\olddb\\" + forum.getId() + ".db";
//			logger.info("---------------------------------------------------------------\n");
//			logger.info(count + " " + olddb + " " + forum.getSite().getId());
//				processSingleForum();
//			}
//		}
		
		
		
		
		logger.info("END");
		
	}


	private static void initDomainMapper() {
		globaldomainmapper.put("f1.avzcy.info", "av");
		globaldomainmapper.put("tk.97ro.com", "av");
		globaldomainmapper.put("tk.avzca.info", "av");
		globaldomainmapper.put("tk.avzck.info", "av");
		globaldomainmapper.put("tk.avzcy.info", "av");

		globaldomainmapper.put("gotoxxxooo.hot99.info", "btgc");
		
		globaldomainmapper.put("97.99bitgc.info", "btgc");
		globaldomainmapper.put("99.15bt.info", "btgc");
		globaldomainmapper.put("99.99btgongchang.info", "btgc");
		globaldomainmapper.put("99.99btgongchang.com", "btgc");
		globaldomainmapper.put("99.99btgc.com", "btgc");
		globaldomainmapper.put("mi.99btgongchang.info", "btgc");
		
		globaldomainmapper.put("www.aisex.com", "aisex");
		globaldomainmapper.put("208.94.244.98", "aisex");
	}




	
	private static int testbadid(String tablename) {
		String sql = "SELECT * FROM " + tablename + " ORDER BY id ASC";
		ResultSet rs;
		int goodnum = 0;
		int badnum = 0;;
		try {
			rs = oldstat.executeQuery(sql);
			while (rs.next()) {
				String id = rs.getString("id");
				
				if(id.indexOf("-") != 6){
					logger.info("" + id);
					badnum++;
				}
				else{
					goodnum++;
				}
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		logger.info("bad:" + badnum + "  good:" + goodnum);
		return badnum;
	}
	
	static Set<String> badurls = new HashSet<String>();

	private static int testbadurl(String tablename,String fieldname) {
		String sql = "SELECT * FROM " + tablename + " ORDER BY id ASC";
		ResultSet rs;
		int goodnum = 0;
		int badnum = 0;;
		try {
			rs = oldstat.executeQuery(sql);
			while (rs.next()) {
				if(currFourm == null){
					String theurl = rs.getString(fieldname);
					String thedomain = StringTool.getDomain(theurl);
					badurls.add(thedomain);
//					logger.info("" + theurl + thedomain);
					
					badnum++;
					continue;
				}
				
				if(!rs.getString(fieldname).contains(currFourm.getSite().getDomain())){
					String theurl = rs.getString(fieldname);
					String thedomain = StringTool.getDomain(theurl);
					badurls.add(thedomain);
//					logger.info("" + theurl + thedomain);
					
					badnum++;
				}
				else{
					goodnum++;
				}
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
		for(String thedomain: badurls){
			if(currFourm == null){
				logger.info("==:" + thedomain);
			}
			else{
				logger.info("==:" + thedomain + "   " + currFourm.getSite().getDomain());
			}
		}
		
		logger.info("bad:" + badnum + "  good:" + goodnum);
		return badnum;
	}
	
	
	
	

	private static void selectpost() {
		String sql = "SELECT * FROM post ORDER BY id ASC";
		ResultSet rs;
		try {
			rs = oldstat.executeQuery(sql);
			while (rs.next()) {
				count++;
				processPost(rs);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}



	private static void selectimage() {
		String sql = "SELECT * FROM image ORDER BY id ASC";
		ResultSet rs;
		try {
			rs = oldstat.executeQuery(sql);
			while (rs.next()) {
				count++;
				String id = rs.getString("id");
				
				if(id.indexOf("-") != 6){
					logger.info("===================id" + id);
				}
				else{
					processimage(rs);
				}
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	private static void processPost(ResultSet rs) throws SQLException {
		String id = rs.getString("id");
		String title = rs.getString("title");
		String url = rs.getString("url");
		String storedurl = url;
		String markstate = rs.getString("markstate");
		int isbted = rs.getInt("isbted");
		
		//id
		String datestr = id.substring(0,6);
		Date date = null;
		try {
			date = sdf.parse(datestr);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		long time = date.getTime();
		
		//url
		String thedomain = StringTool.getDomain(url);
		if(thedomain.length() < 4 || thedomain.length() > 20){
			logger.info("===================url" + url);
		}
		else{
			storedurl = url.replace(thedomain, "DOMAIN"+currFourm.getSite().getId());
		}

//		logger.info(count + ":" + time + " - " + title + markstate + isbted );
		logger.info(count);
		
		Post post = new Post();
		post.postid = time*100000+count+"";
		post.createtime = time;
		post.modifytime = time;
		
		post.siteid = currFourm.getSite().getId();
		post.forumid = currFourm.getSite().getDomain();
		post.posttitle = title;
		post.posturl = url;
		post.storedposturl = storedurl;
		post.markedimgnum =1;
		if(isbted == 1){
			post.markedimgnum = 1;
			post.processed = true;
		}
		else{
			post.markedimgnum = 0;
			post.processed = false;
		}
		post.markedimgstate = markstate;
		
		insert(post);
		
		
	}
	
	
	

	private static void processimage(ResultSet rs) throws SQLException {
		String id = rs.getString("id");
		int ismarked = rs.getInt("ismarked");
		String url = rs.getString("url");
		String posturl = rs.getString("posturl");
		String posttitle = rs.getString("posttitle");
		long length = rs.getLong("length");
		String md5 = rs.getString("md5");
		String lengthmd5 = rs.getString("lengthmd5");
		String markstate = rs.getString("markstate");
		

		
		//id
		String datestr = id.substring(0,6);
		Date date = null;
		try {
			date = sdf.parse(datestr);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		long time = date.getTime();

		
		//url
		String storedurl = posturl;
		String thedomain = StringTool.getDomain(url);
		if(thedomain.length() < 4 || thedomain.length() > 20){
			logger.info("===================url" + url);
		}
		else{
			storedurl = url.replace(thedomain, "DOMAIN"+currFourm.getSite().getId());
		}
		
		
				

//		logger.info(count + ":" + time + " - " + ismarked + markstate );
		logger.info(count);
		
		Image image = new Image();
		image.imageid =  time*100000+count+"";
		image.createtime = time;
		image.modifytime = time;
		image.imageurl = url;
//		image.posttitle = posttitle;
//		image.posturl = storedurl;
		image.length = length;
		image.md5 = md5;
		image.lengthmd5 = lengthmd5;
		
		
		if(ismarked == 1){
			image.marked = true;
		}
		else{
			image.marked = false;
		}
		
		image.markstate = markstate;
		
		insertImage(image);
		
		
	}

	
	
	
	public static void insert(Post post) {
		String sqlstr = "insert into post values("+"?,?,?,?,?,"+"?,?,?,?,?,"+"?,?,?,?,?,"+"?,?,?,?,?,"+"?,?,?,?,?,"+"?,?,?,?,?,"+"?)";
		try {
			PreparedStatement prep = conn.prepareStatement(sqlstr);
			prep.setString(1,post.postid);
			prep.setString(2,post.posttitle);
			prep.setInt(3,post.effectiveimgnum);
			prep.setInt(4,post.viewedimgnum);
			prep.setInt(5,post.markedimgnum);
			prep.setBoolean(6,post.taged);
			prep.setBoolean(7,post.processed);
			prep.setInt(8,post.score);
			prep.setString(9,post.tags);
			prep.setString(10,post.posturl);
			prep.setString(11,post.siteid);
			prep.setString(12,post.forumid);
			prep.setString(13,post.runid);
			
			prep.setString(14,post.storedposturl);
			prep.setString(15,post.content);
			prep.setString(16,post.links);
			prep.setInt(17,post.seqinlist);
			prep.setInt(18,post.sizeoflist);
			prep.setInt(19,post.imgnum);
			prep.setInt(20,post.linknum);
			prep.setInt(21,post.effectivelinknum);
			prep.setString(22,post.scoretrace);
			prep.setString(23,post.dupstate);
			prep.setString(24,post.viewedimgstate);
			prep.setString(25,post.markedimgstate);
			prep.setString(26,post.processstate);
			prep.setString(27,post.healthstate);
			prep.setString(28,post.statecode);
			prep.setString(29,post.imagestatesummary);
			
			prep.setLong(30,post.createtime);
			prep.setLong(31,post.modifytime);
			
			prep.executeUpdate();
			
			prep.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	

	public static void insertImage(Image image) {
		
		String sqlstr = "insert into image values("+"?,?,?,?,?,"+"?,?,?,?,?,"+"?,?,?,?,?,"+"?,?,?,?,?,"+"?,?,?,?,?,"+"?,?,?,?,?,"+"?,?,?,?,?)";
		
		try {
			PreparedStatement prep = conn.prepareStatement(sqlstr);
			prep.setString(1,image.imageid);
			prep.setString(2,image.imageurl);
			prep.setInt(3,image.effectiveimgindex);
			prep.setBoolean(4,image.downloaded);
			prep.setBoolean(5,image.dupped);
			prep.setString(6,image.dupstate);
			prep.setBoolean(7,image.viewed);
			prep.setBoolean(8,image.marked);
			prep.setString(9,image.markstate);
			prep.setString(10,image.lengthmd5);
			
			
			prep.setString(11,image.postid);
//			prep.setString(12,image.posttitle);
//			prep.setInt(13,image.effectiveimgnum);
//			prep.setInt(14,image.viewedimgnum);
//			prep.setInt(15,image.markedimgnum);
//			prep.setBoolean(16,image.taged);
//			prep.setBoolean(17,image.processed);
//			prep.setInt(18,image.score);
//			prep.setString(19,image.tags);
//			prep.setString(20,image.posturl);
			prep.setString(21,image.siteid);
			prep.setString(22,image.forumid);
			prep.setString(23,image.runid);
			
			
			prep.setBoolean(24,image.existed);
			prep.setString(25,image.healthstate);
			prep.setString(26,image.statecode);
			prep.setInt(27,image.imgindex);
			prep.setString(28,image.relativepath);
			prep.setInt(29,image.width);
			prep.setInt(30,image.height);
			prep.setLong(31,image.length);
			prep.setString(32,image.md5);
			prep.setString(33,image.scoretrace);
			prep.setLong(34,image.createtime);
			prep.setLong(35,image.modifytime);
			
			prep.executeUpdate();
			
			prep.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	

	private static void testtime() {
		long time = System.currentTimeMillis();
		logger.info(time);
		
		logger.info(sdf.format(time));
		
		
		Date date = null;
		try {
			date = sdf.parse("130418");
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		long time2 = date.getTime();
		logger.info("time2:" + time2);
		
		logger.info(sdf.format(time2));
	}
}
